package repositories;

import java.util.Collection;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import domain.AvailableItem;

@Repository
public interface AvailableItemRepository extends
		JpaRepository<AvailableItem, Integer> {

	@Query("select ai from AvailableItem ai where ai.availableUnits>=1")
	Collection<AvailableItem> findAvailableItems();

	@Query("select ai from AvailableItem ai where ai.availableUnits>=1 and not  ai.seller.id = ?1")
	Collection<AvailableItem> findAvailableItemsByOthersCustomer(int cutomerId);

	@Query("select ai from AvailableItem ai where ai.seller.id=?1")
	Collection<AvailableItem> findAvailableItemsBySellerId(int sellerId);

	@Query("select c.availableItem.id, avg(c.rating) from Comment c where c.availableItem.seller.id=?1 group by c.availableItem.id")
	Collection<Object[]> findBestRatedBySeller(int sellerId);

	@Query("select ai.id from AvailableItem ai where ai.referenceNumber=?1")
	int findAvailableItemIdByPurchasedItemRefNumber(String ref);

	@Query("select c.wishList from Customer c where c.id = ?1")
	Collection<AvailableItem> findWishedItemsByCustomer(int customerId);

	@Query("select u.availableItem from ShoppingCartItemUnit u where u.customer.id = ?1")
	Collection<AvailableItem> findCartItemsByCustomer(int customerId);
}
